<!-- getchecklist2.php
     A PHP script to access the advising database through MySQL
     -->
<html>
<head>
<title> get checklist </title>
</head>
<body>
<?php
// Connect to MySQL

$db = new mysqli("localhost", "root", "", "db4208");
if ($db->connect_errno) {
    echo "Failed to connect to MySQL: (" . $db->connect_errno . ") " . $db->connect_error;
}

// Get the query and clean it up (delete leading and trailing
// whitespace and remove backslashes from magic_quotes_gpc)
$param = $_POST['sid'];
trim($param);
$param = stripslashes($param);

// Get information of non-6990 courses
// step 1: construct the SQL Prepared statement
$pstmtstr = 'SELECT C.major, O.coursenum, C.title, O.year, O.semester, C.area_1, T.grade '
		.  'FROM takes T JOIN offer O ON(T.offerid=O.offerid) '
		.  'JOIN course C ON(O.major=C.major AND O.coursenum=C.coursenum) '
		.  'WHERE O.coursenum <> 6990 AND T.studentID=?' ;
// print "<p> pstmt=" . $pstmtstr . "</p>";

if (!($pstmt = $db->prepare($pstmtstr))) {
	print "<p> Prepare failed: (" . $pstmt->errno . ") " . $pstmt->error . "</p>";
}

// step 2: bind prepared statement's parameter with value 
if (!$pstmt->bind_param("i", $param)) {
	print "<p> Binding parameters failed: (" . $pstmt->errno . ") " . $pstmt->error . "</p>";
}

// step 3: execute the prepared SQL statement
$pstmt->execute();
$result = $pstmt->get_result();
if (!$result) {
	print "<p> Error - the first query could not be executed </p>";
	print "<p> param=" . $param . "</p>";
}

// Display the results in a table
print "<table><caption> <h3> Query Results </h3> </caption> <tbody>";
// Output the values of the fields in the rows by looping through the result set 
while ($row = mysqli_fetch_assoc($result)){
	$values = array_values($row);
	print "<tr>";
	print "<td>" . $values[0] . "</td>";
	print "<td>" . $values[1] . "</td>";
	print "<td>" . $values[2] . "</td>";
	print "<td>" . $values[3] . "</td>";
	print "<td>" . $values[4] . "</td>";
	print "<td>" . $values[5] . "</td>";
	print "<td>" . $values[6] . "</td>";
	print "</tr>";
}

// Get information of 6990 courses
// step 1: construct the SQL statement
$pstmtstr2 = 'SELECT C.major, O.coursenum, O.title, O.year, O.semester, O.area_1, T.grade '
		.  'FROM takes T JOIN offer O ON(T.offerid=O.offerid) '
		.  'JOIN course C ON(O.major=C.major AND O.coursenum=C.coursenum) '
		.  'WHERE O.coursenum=6990 AND T.studentID=?'; 

if (!($pstmt2 = $db->prepare($pstmtstr2))) {
	print "<tr> Prepare failed: (" . $pstmt2->errno . ") " . $pstmt2->error . "</tr>";
}

// step 2: bind 2nd prepared statement's parameter with value
if (!$pstmt2->bind_param("i", $param)) {
	print "<tr> Binding parameters failed: (" . $pstmt2->errno . ") " . $pstmt2->error . "</tr>";
}

// step 3: execute the prepared SQL statement
$pstmt2->execute();
$result2 = $pstmt2->get_result();
if (!$result2) {
	print "<tr> Error - the second query could not be executed </tr>";
}

// Output the values of the fields in the rows
while ($row = mysqli_fetch_assoc($result2)) {
	$values = array_values($row);
	print "<tr>";
	print "<td>" . $values[0] . "</td>";
	print "<td>" . $values[1] . "</td>";
	print "<td>" . $values[2] . "</td>";
	print "<td>" . $values[3] . "</td>";
	print "<td>" . $values[4] . "</td>";
	print "<td>" . $values[5] . "</td>";
	print "<td>" . $values[6] . "</td>";
	print "</tr>";
}
print "</tbody></table>";
?>
</body>
</html>